﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DealerJudge.Entity;
using System.Data.SqlClient;

namespace DealerJudge.Data
{
    public class DealerData : IDealerData 
    {
        private ISqlHelper _sqlHelper;
        private string _searchStoredProcName = "p_dj_search_dealer";
        private const string DealerIdColName = "Id";
        private const string DealerNameColName = "Name";
        private const string DealerRatingColName = "Rating";
        private const string DealerReviewCountColName = "ReviewCount";
        
        public DealerData(ISqlHelper sqlHelper)
        {
            _sqlHelper = sqlHelper;
        }
        public List<Dealer> Search(DealerCategory dealerCategory, City city, Manufacturer manufacturer, Location location)
        {
            List<Dealer> filteredDealers = new List<Dealer>();
            List<IDbDataParameter> paramList = new List<IDbDataParameter>
            {
                new DataParameter{ParameterName = "@Category", Value = dealerCategory},
                new DataParameter{ParameterName = "@CityId", Value = city.Id},
                new DataParameter{ParameterName = "@ManufacturerId", Value = manufacturer.Id},
                new DataParameter{ParameterName = "@LocationId", Value = location.Id}
            };
            DataTable dealerSearchDataTable = _sqlHelper.ExecuteDataTable(CommandType.StoredProcedure, _searchStoredProcName, paramList);
            foreach(DataRow dealerRow in dealerSearchDataTable.Rows)
            {
                Dealer dealer = new Dealer
                {
                    Id = Convert.ToInt32(dealerRow[DealerIdColName]),
                    Name = Convert.ToString(dealerRow[DealerNameColName]),
                    Rating = Convert.ToInt32(dealerRow[DealerRatingColName]),
                    ReviewCount = Convert.ToInt32(dealerRow[DealerReviewCountColName]),
                };
                filteredDealers.Add(dealer);
            }
            return filteredDealers;
        }
        public List<Category> GetCategory()
        {
            List<Category> categoryList = new List<Category>();
            DataTable categoryDataTable = _sqlHelper.ExecuteDataTable(CommandType.Text, "Select * from Category");
            foreach (DataRow categoryData in categoryDataTable.Rows)
            {
                Category category = new Category
                {
                   Id = Convert.ToInt32(categoryData["id"]),
                   Name = categoryData["name"].ToString()
                };
                categoryList.Add(category);
            }
            return categoryList;
        }

        public List<City> GetAllCities()
        {
            List<City> cityList = new List<City>();
            DataTable cityDataTable = _sqlHelper.ExecuteDataTable(CommandType.Text, "Select * from City");
            foreach (DataRow cityData in cityDataTable.Rows)
            {
                City city = new City
                {
                    Id = Convert.ToInt32(cityData["id"]),
                    Name = cityData["name"].ToString()
                };
                cityList.Add(city);
            }
            return cityList;
        }

        public List<City> GetCityById(int id)
        {
            List<City> cityList = new List<City>();
            DataTable cityDataTable = _sqlHelper.ExecuteDataTable(CommandType.Text, string.Format("Select * from City where Id={0}", id));
            foreach (DataRow cityData in cityDataTable.Rows)
            {
                City city = new City
                {
                    Id = Convert.ToInt32(cityData["id"]),
                    Name = cityData["name"].ToString()
                };
                cityList.Add(city);
            }
            return cityList;
        }

        public List<City> GetCitiesByCategory(int cat_id)
        {
            List<City> cityList = new List<City>();
            List<IDbDataParameter> param = new List<IDbDataParameter>();
            param.Add(new SqlParameter("@cat_id", cat_id));
            DataTable cityDataTable = _sqlHelper.ExecuteDataTable(CommandType.StoredProcedure, string.Format("getCitiesByCategory"), param);

            foreach (DataRow cityData in cityDataTable.Rows)
            {
                City city = new City
                {
                    Id = Convert.ToInt32(cityData["Id"]),
                    Name = cityData["name"].ToString()
                };
                cityList.Add(city);
            }
            return cityList;
        }

        public List<Manufacturer> GetManufacturerByCityAndCategory(int city_id, int cat_id)
        {
            List<Manufacturer> manufacturerList = new List<Manufacturer>();
            List<IDbDataParameter> param = new List<IDbDataParameter>();
            param.Add(new SqlParameter("@city_id", city_id));
            param.Add(new SqlParameter("@cat_id", cat_id));
            DataTable manufacturerDataTable = _sqlHelper.ExecuteDataTable(CommandType.StoredProcedure, string.Format("getManufacturerByCityAndCategory"), param);

            foreach (DataRow manufacturerData in manufacturerDataTable.Rows)
            {
                Manufacturer manufacturer = new Manufacturer
                {
                    Id = Convert.ToInt32(manufacturerData["Id"]),
                    Name = manufacturerData["name"].ToString()
                };
                manufacturerList.Add(manufacturer);
            }
            return manufacturerList;
        }
    }
}
